package com.yix.demo;

import com.yix.util.DBUtil;

import java.sql.*;

public class TableDemo {
    public static void main(String[] args) {
        deleteData();
        queryData();
    }

    private static void createTable() {
        String createTableSQL = "CREATE TABLE IF NOT EXISTS employees (" +
                "id SERIAL PRIMARY KEY," +
                "first_name VARCHAR(50) NOT NULL," +
                "last_name VARCHAR(50) NOT NULL," +
                "email VARCHAR(100) UNIQUE," +
                "hire_date DATE," +
                "salary NUMERIC(10, 2)" +
                ");";

        try (Connection conn = DBUtil.getConnection();
             Statement stmt = conn.createStatement()) {
            stmt.execute(createTableSQL);
            System.out.println("表创建成功");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static void createSchemaTable() {
        String databaseName = "my_database"; // 替换为你的数据库名称
        String schemaName = "tp"; // 替换为你的模式名称
        String tableName = "user"; // 替换为你的表名称
        String tablespaceName = "my_tablespace"; // 替换为你的表空间名称

        String createTableSQL = "CREATE TABLE IF NOT EXISTS " + schemaName + "." + tableName + " (" +
                "id SERIAL PRIMARY KEY," +
                "name VARCHAR(50) NOT NULL," +
                "email VARCHAR(100) UNIQUE" +
                ") ; ";

        try (Connection conn = DBUtil.getConnection();
             Statement stmt = conn.createStatement()) {
            stmt.execute("SET search_path TO " + schemaName + ";");
            stmt.execute(createTableSQL);
            System.out.println("表 '" + tableName + "' 创建成功");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static void insertData() {
        String databaseName = "my_database"; // 替换为你的数据库名称
        String schemaName = "tp"; // 替换为你的模式名称
        String tableName = "user"; // 替换为你的表名称

        String insertDataSQL = "INSERT INTO " + schemaName + "." + tableName + " (name, email) VALUES (?, ?);";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(insertDataSQL)) {
            pstmt.setString(1, "wdx");
            pstmt.setString(2, "wdx@moonshot.cn");
            int rowsAffected = pstmt.executeUpdate();
            System.out.println(rowsAffected + " 行受影响");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static void queryData() {
        String databaseName = "my_database"; // 替换为你的数据库名称
        String schemaName = "tp"; // 替换为你的模式名称
        String tableName = "user"; // 替换为你的表名称

        String queryDataSQL = "SELECT * FROM " + schemaName + "." + tableName + ";";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(queryDataSQL);
             ResultSet rs = pstmt.executeQuery()) {
            while (rs.next()) {
                System.out.println("ID: " + rs.getInt("id") +
                        ", Name: " + rs.getString("name") +
                        ", Email: " + rs.getString("email"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static void updateData() {
        String databaseName = "my_database"; // 替换为你的数据库名称
        String schemaName = "tp"; // 替换为你的模式名称
        String tableName = "user"; // 替换为你的表名称

        String updateDataSQL = "UPDATE " + schemaName + "." + tableName + " SET email = ? WHERE name = ?;";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(updateDataSQL)) {
            pstmt.setString(1, "kimi1@moonshot.cn");
            pstmt.setString(2, "Kimi");
            int rowsAffected = pstmt.executeUpdate();
            System.out.println(rowsAffected + " 行受影响");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static void deleteData() {
        String schemaName = "tp";
        String deleteSql = " DELETE FROM " + schemaName + "." + "user WHERE name =?;";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(deleteSql)) {
            pstmt.setString(1, "Kimi");
            int rowsAffected = pstmt.executeUpdate();
            System.out.println(rowsAffected + " 行受影响");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

}
